package com.netsdk.demo.util.sql;

import com.netsdk.demo.util.DateUtil;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


public class JdbcUtils {

    private static String jdbcUrl;
    private static String username;
    private static String password;

    static {
        try {
            //获取参数
            jdbcUrl = "jdbc:mysql://127.0.0.1:3306/juyuan";
            username = "root";
            password = "root";

            //加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接对象
     *
     * @return java.sql.Connection 数据库连接对象
     */
    public static Connection getConnection() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(jdbcUrl, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    /**
     * 关闭资源方法
     *
     * @param connection 数据库连接对象资源
     * @param statement  数据库 SQL 语句搬运工资源
     */
    public static void close(Connection connection, Statement statement) {
        close(statement, connection);
    }

    /**
     * 关闭资源方法
     *
     * @param connection 数据库连接对象资源
     * @param statement  数据库 SQL 语句搬运工资源
     * @param resultSet  数据库查询结果集对象
     */
    public static void close(Connection connection, Statement statement, ResultSet resultSet) {
        close(resultSet, statement, connection);
    }

    /**
     * 内部私有化方法，统一处理数据库相关资源
     *
     * @param resources 不定长参数，统一关闭对应资源
     */
    private static void close(AutoCloseable... resources) {
        if (resources != null && resources.length > 0) {
            Arrays.stream(resources).forEach(source -> {
                try {
                    if (source != null) {
                        source.close();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            });
        }
    }

    /**
     * 预处理SQL,获取PreparedStatement对象，并对SQL参数进行赋值操作
     *
     * @param connection 数据库连接对象
     * @param sql        sql语句
     * @param parameters 实际参数
     * @return 返回PreparedStatement对象
     * @throws SQLException SQL异常
     */
    private static PreparedStatement handlePreparedStatement(Connection connection, String sql, Object[] parameters) throws SQLException {
        //预处理 SQL 语句，得到PreparedStatement对象
        PreparedStatement statement = connection.prepareStatement(sql);

        //获取数据库元数据，并通过元数据获取SQL语句参数个数
        int parameterCount = statement.getParameterMetaData().getParameterCount();

        //条件约束，判断是否需要进行参数赋值操作
        if (parameterCount != 0 && parameters != null && parameters.length == parameterCount) {
            for (int i = 0; i < parameterCount; i++) {
                statement.setObject(i + 1, parameters[i]);
            }
        }

        return statement;
    }

    /**
     * 是否为空字符
     */
    public static boolean isEmpty(String str) {
        if (str == null || str.trim().length() == 0) {
            return true;
        }
        if ("null".equalsIgnoreCase(str) || "undefined".equalsIgnoreCase(str)) {
            return true;
        }
        return false;
    }

    /**
     * 更新方法，支持 insert delete update 操作
     *
     * @return SQL 语句执行对应数据表的影响行数。
     */
    public static int insert(EventDto eventDto) {
        String table = "INSERT INTO jyxq_qxjt_event ( ";
        StringBuilder filed = new StringBuilder();
        StringBuilder values = new StringBuilder();

        if(!isEmpty(eventDto.getEvent_code())){
            filed.append("event_code,");
            values.append("'"+eventDto.getEvent_code()+"',");
        }
        if(!isEmpty(eventDto.getEvent_id())){
            filed.append("event_id,");
            values.append("'"+eventDto.getEvent_id()+"',");
        }
        if(!isEmpty(eventDto.getEvent_name())){
            filed.append("event_name,");
            values.append("'"+eventDto.getEvent_name()+"',");
        }
        if(!isEmpty(eventDto.getChannel())){
            filed.append("channel,");
            values.append("'"+eventDto.getChannel()+"',");
        }
        if(!isEmpty(eventDto.getEvent_time())){
            filed.append("event_time,");
            values.append("'"+eventDto.getEvent_time()+"',");
        } if(!isEmpty(eventDto.getEvent_utc_time())){
            filed.append("event_utc_time,");
            values.append("'"+eventDto.getEvent_utc_time()+"',");
        } if(!isEmpty(eventDto.getEvent_content())){
            filed.append("event_content,");
            values.append("'"+eventDto.getEvent_content()+"',");
        } if(!isEmpty(eventDto.getImg_fbl())){
            filed.append("img_fbl,");
            values.append("'"+eventDto.getImg_fbl()+"',");
        } if(!isEmpty(eventDto.getImg_name())){
            filed.append("img_name,");
            values.append("'"+eventDto.getImg_name()+"',");
        } if(!isEmpty(eventDto.getImg_mask())){
            filed.append("img_mask,");
            values.append("'"+eventDto.getImg_mask()+"',");
        } if(!isEmpty(eventDto.getEvent_dz())){
            filed.append("event_dz,");
            values.append("'"+eventDto.getEvent_dz()+"',");
        } if(!isEmpty(eventDto.getEvent_class())){
            filed.append("event_class,");
            values.append("'"+eventDto.getEvent_class()+"',");
        } if(!isEmpty(eventDto.getLane())){
            filed.append("lane,");
            values.append("'"+eventDto.getLane()+"',");
        } if(!isEmpty(eventDto.getRegion())){
            filed.append("region,");
            values.append("'"+eventDto.getRegion()+"',");
        } if(!isEmpty(eventDto.getStu_Vehicle_Info())){
            filed.append("stu_Vehicle_Info,");
            values.append("'"+eventDto.getStu_Vehicle_Info()+"',");
        } if(!isEmpty(eventDto.getRule_Id())){
            filed.append("rule_Id,");
            values.append("'"+eventDto.getRule_Id()+"',");
        } if(!isEmpty(eventDto.getObject_id())){
            filed.append("object_id,");
            values.append("'"+eventDto.getObject_id()+"',");
        } if(!isEmpty(eventDto.getObj_name())){
            filed.append("obj_name,");
            values.append("'"+eventDto.getObj_name()+"',");
        } if(!isEmpty(eventDto.getTriggertType())){
            filed.append("triggert_type,");
            values.append("'"+eventDto.getTriggertType()+"',");
        } if(!isEmpty(eventDto.getClean_value())){
            filed.append("clean_value,");
            values.append("'"+eventDto.getClean_value()+"',");
        } if(!isEmpty(eventDto.getSequence())){
            filed.append("sequence,");
            values.append("'"+eventDto.getSequence()+"',");
        } if(!isEmpty(eventDto.getState_num())){
            filed.append("state_num,");
            values.append("'"+eventDto.getState_num()+"',");
        } if(!isEmpty(eventDto.getPreset_id())){
            filed.append("preset_id,");
            values.append("'"+eventDto.getPreset_id()+"',");
        } if(!isEmpty(eventDto.getStop_vehicle_num())){
            filed.append("stop_vehicle_num,");
            values.append("'"+eventDto.getStop_vehicle_num()+"',");
        } if(!isEmpty(eventDto.getDetection_area_vehicle_num())){
            filed.append("detection_area_vehicle_num,");
            values.append("'"+eventDto.getDetection_area_vehicle_num()+"',");
        } if(!isEmpty(eventDto.getEm_overflow_state())){
            filed.append("em_overflow_state,");
            values.append("'"+eventDto.getEm_overflow_state()+"',");
        } if(!isEmpty(eventDto.getnNumber())){
            filed.append("nNumber,");
            values.append("'"+eventDto.getnNumber()+"',");
        } if(!isEmpty(eventDto.getnEnteredNumber())){
            filed.append("nEnteredNumber,");
            values.append("'"+eventDto.getnEnteredNumber()+"',");
        } if(!isEmpty(eventDto.getnExitedNumber())){
            filed.append("nExitedNumber,");
            values.append("'"+eventDto.getnExitedNumber()+"',");
        } if(!isEmpty(eventDto.getEmType())){
            filed.append("emType,");
            values.append("'"+eventDto.getEmType()+"',");
        } if(!isEmpty(eventDto.getnAction())){
            filed.append("nAction,");
            values.append("'"+eventDto.getnAction()+"',");
        } if(!isEmpty(eventDto.getEmTriggerType())){
            filed.append("emTriggerType,");
            values.append("'"+eventDto.getEmTriggerType()+"',");
        } if(!isEmpty(eventDto.getnAlarmIntervalTime())){
            filed.append("nAlarmIntervalTime,");
            values.append("'"+eventDto.getnAlarmIntervalTime()+"',");
        } if(!isEmpty(eventDto.getnContainerTextNum())){
            filed.append("nContainerTextNum,");
            values.append("'"+eventDto.getnContainerTextNum()+"',");
        } if(!isEmpty(eventDto.getEmCaptureProcess())){
            filed.append("emCaptureProcess,");
            values.append("'"+eventDto.getEmCaptureProcess()+"',");
        } if(!isEmpty(eventDto.getStuStartTime())){
            filed.append("stuStartTime,");
            values.append("'"+eventDto.getStuStartTime()+"',");
        } if(!isEmpty(eventDto.getStuEndTime())){
            filed.append("stuEndTime,");
            values.append("'"+eventDto.getStuEndTime()+"',");
        }if(!isEmpty(eventDto.getnManListCount())){
            filed.append("nManListCount,");
            values.append("'"+eventDto.getnManListCount()+"',");
        }
        if(!isEmpty(eventDto.getnStature())){
            filed.append("nStature,");
            values.append("'"+eventDto.getnStature()+"',");
        }if(!isEmpty(eventDto.getnPreserId())){
            filed.append("nPreserID,");
            values.append("'"+eventDto.getnPreserId()+"',");
        }
        if(!isEmpty(eventDto.getSzPresetName())){
            filed.append("szPresetName,");
            values.append("'"+eventDto.getSzPresetName()+"',");
        }if(!isEmpty(eventDto.getnParkingInfoNum())){
            filed.append("nParkingInfoNum,");
            values.append("'"+eventDto.getnParkingInfoNum()+"',");
        }if(!isEmpty(eventDto.getLongitude())){
            filed.append("longitude,");
            values.append("'"+eventDto.getLongitude()+"',");
        }if(!isEmpty(eventDto.getLatidude())){
            filed.append("latidude,");
            values.append("'"+eventDto.getLatidude()+"',");
        }if(!isEmpty(eventDto.getHeight())){
            filed.append("height,");
            values.append("'"+eventDto.getHeight()+"',");
        }if(!isEmpty(eventDto.getAngle())){
            filed.append("angle,");
            values.append("'"+eventDto.getAngle()+"',");
        }if(!isEmpty(eventDto.getDbSpeed())){
            filed.append("dbSpeed,");
            values.append("'"+eventDto.getDbSpeed()+"',");
        }if(!isEmpty(eventDto.getDbTriggerSpeed())){
            filed.append("dbTriggerSpeed,");
            values.append("'"+eventDto.getDbTriggerSpeed()+"',");
        }if(!isEmpty(eventDto.getbRunType())){
            filed.append("bRunType,");
            values.append("'"+eventDto.getbRunType()+"',");
        }if(!isEmpty(eventDto.getnImageInfoNum())){
            filed.append("nImageInfoNum,");
            values.append("'"+eventDto.getnImageInfoNum()+"',");
        }if(!isEmpty(eventDto.getnObjectCount())){
            filed.append("nObjectCount,");
            values.append("'"+eventDto.getnObjectCount()+"',");
        }if(!isEmpty(eventDto.getnCrowdDetectNum())){
            filed.append("nCrowdDetectNum,");
            values.append("'"+eventDto.getnCrowdDetectNum()+"',");
        }if(!isEmpty(eventDto.getStuSceneImage())){
            filed.append("stuSceneImage,");
            values.append("'"+eventDto.getStuSceneImage()+"',");
        }
        if(!isEmpty(eventDto.getpBuffer())){
            filed.append("pBuffer,");
            values.append("'"+eventDto.getpBuffer()+"',");
        }
        if(!isEmpty(eventDto.getSzName())){
            filed.append("szName,");
            values.append("'"+eventDto.getSzName()+"',");
        }
        if(!isEmpty(eventDto.getnWorkAction())){
            filed.append("nWorkAction,");
            values.append("'"+eventDto.getnWorkAction()+"',");
        }
        if(!isEmpty(eventDto.getnWorkActionNum())){
            filed.append("nWorkActionNum,");
            values.append("'"+eventDto.getnWorkActionNum()+"',");
        }
        if(!isEmpty(eventDto.getSzVideoPath())){
            filed.append("szVideoPath,");
            values.append("'"+eventDto.getSzVideoPath()+"',");
        }
        if(!isEmpty(eventDto.getnVisibility())){
            filed.append("nVisibility,");
            values.append("'"+eventDto.getnVisibility()+"',");
        }
        if(!isEmpty(eventDto.getBigPicture())){
            filed.append("bigPicture,");
            values.append("'"+eventDto.getBigPicture()+"',");
        }
        if(!isEmpty(eventDto.getScenePicture())){
            filed.append("scenePicture,");
            values.append("'"+eventDto.getScenePicture()+"',");
        }
        if(!isEmpty(eventDto.getFacePicture())){
            filed.append("facePicture,");
            values.append("'"+eventDto.getFacePicture()+"',");
        }
        if(!isEmpty(eventDto.getnLane())){
            filed.append("nLane,");
            values.append("'"+eventDto.getnLane()+"',");
        }
        if(!isEmpty(eventDto.getDwState())){
            filed.append("dwState,");
            values.append("'"+eventDto.getDwState()+"',");
        }
        if(!isEmpty(eventDto.getEmDrivingDir())){
            filed.append("emDrivingDir,");
            values.append("'"+eventDto.getEmDrivingDir()+"',");
        }
        if(!isEmpty(eventDto.getfAverageSpeed())){
            filed.append("fAverageSpeed,");
            values.append("'"+eventDto.getfAverageSpeed()+"',");
        }
        if(!isEmpty(eventDto.getnLargeVehicles())){
            filed.append("nLargeVehicles,");
            values.append("'"+eventDto.getnLargeVehicles()+"',");
        }
        if(!isEmpty(eventDto.getnMediumVehicles())){
            filed.append("nMediumVehicles,");
            values.append("'"+eventDto.getnMediumVehicles()+"',");
        }
        if(!isEmpty(eventDto.getnSmallVehicles())){
            filed.append("nSmallVehicles,");
            values.append("'"+eventDto.getnSmallVehicles()+"',");
        }
        if(!isEmpty(eventDto.getnMotoVehicles())){
            filed.append("nMotoVehicles,");
            values.append("'"+eventDto.getnMotoVehicles()+"',");
        }
        if(!isEmpty(eventDto.getnLeftVehicles())){
            filed.append("nLeftVehicles,");
            values.append("'"+eventDto.getnLeftVehicles()+"',");
        }
        if(!isEmpty(eventDto.getnRightVehicles())){
            filed.append("nRightVehicles,");
            values.append("'"+eventDto.getnRightVehicles()+"',");
        }
        if(!isEmpty(eventDto.getnStraightVehicles())){
            filed.append("nStraightVehicles,");
            values.append("'"+eventDto.getnStraightVehicles()+"',");
        }
        if(!isEmpty(eventDto.getnUTurnVehicles())){
            filed.append("nUTurnVehicles,");
            values.append("'"+eventDto.getnUTurnVehicles()+"',");
        }
        if(!isEmpty(eventDto.getnBusVehicles())){
            filed.append("nBusVehicles,");
            values.append("'"+eventDto.getnBusVehicles()+"',");
        }
        if(!isEmpty(eventDto.getnMPVVehicles())){
            filed.append("nMPVVehicles,");
            values.append("'"+eventDto.getnMPVVehicles()+"',");
        }
        if(!isEmpty(eventDto.getnMidPassengerCarVehicles())){
            filed.append("nMidPassengerCarVehicles,");
            values.append("'"+eventDto.getnMidPassengerCarVehicles()+"',");
        }
        if(!isEmpty(eventDto.getnMiniCarriageVehicles())){
            filed.append("nMiniCarriageVehicles,");
            values.append("'"+eventDto.getnMiniCarriageVehicles()+"',");
        }
        if(!isEmpty(eventDto.getnOilTankTruckVehicles())){
            filed.append("nOilTankTruckVehicles,");
            values.append("'"+eventDto.getnOilTankTruckVehicles()+"',");
        }
        if(!isEmpty(eventDto.getnSUVVehicles())){
            filed.append("nSUVVehicles,");
            values.append("'"+eventDto.getnSUVVehicles()+"',");
        }
        if(!isEmpty(eventDto.getnPickupVehicles())){
            filed.append("nPickupVehicles,");
            values.append("'"+eventDto.getnPickupVehicles()+"',");
        }
        if(!isEmpty(eventDto.getnSUVorMPVVehicles())){
            filed.append("nSUVorMPVVehicles,");
            values.append("'"+eventDto.getnSUVorMPVVehicles()+"',");
        }
        if(!isEmpty(eventDto.getnTankCarVehicles())){
            filed.append("nTankCarVehicles,");
            values.append("'"+eventDto.getnTankCarVehicles()+"',");
        }
        if(!isEmpty(eventDto.getEmCustomFlowAttribute())){
            filed.append("emCustomFlowAttribute,");
            values.append("'"+eventDto.getEmCustomFlowAttribute()+"',");
        }



        filed.append("create_time");
        values.append("'"+ DateUtil.getTime() +"'");

        table = table + filed.toString() + ")  values (" + values.toString() +")";
        System.out.println("全系交通sql = "+table);

        // 1. 准备必要的变量
        int affectedRows = 0;
        Connection connection = null;
        PreparedStatement statement = null;

        // 2. 利用 util.JdbcUtils 工具类获取数据库连接对象
        connection = JdbcUtils.getConnection();

        try {
            statement = handlePreparedStatement(connection, table, null);
            // 5. 执行 SQL 语句，得到受影响的行数
            affectedRows = statement.executeUpdate();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.close(connection, statement);
        }

        return affectedRows;
    }

    public static int update(String sql) {
        // 1. 准备必要的变量
        int affectedRows = 0;
        Connection connection = null;
        PreparedStatement statement = null;

        // 2. 利用 util.JdbcUtils 工具类获取数据库连接对象
        connection = JdbcUtils.getConnection();

        try {
            statement = handlePreparedStatement(connection, sql, null);
            // 5. 执行 SQL 语句，得到受影响的行数
            affectedRows = statement.executeUpdate();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.close(connection, statement);
        }

        return affectedRows;

    }
//
//    public static void main(String[] args) {
//        JdbcUtils.update("update trajectory t " +
//                " set time=DATE_FORMAT(DATE_ADD(STR_TO_DATE(t.time,'%Y-%m-%d %H:%i:%s'),INTERVAL t.orderno+t.delay SECOND),'%Y-%m-%d %H:%i:%s')," +
//                " t.state=1  where t.state=0");
//        System.out.println("dfdasfsdf");
//    }
    /**
     * 更新方法，支持 insert delete update 操作
     *
     * @return SQL 语句执行对应数据表的影响行数。
     */
    public static int insert(Trajectory eventDto) {
        String table = "INSERT INTO trajectory ( ";
        StringBuilder filed = new StringBuilder();
        StringBuilder values = new StringBuilder();

        if(!isEmpty(eventDto.getGreen())){
            filed.append("green,");
            values.append("'"+eventDto.getGreen()+"',");
        }
        if(!isEmpty(eventDto.getnLaneNo())){
            filed.append("nlaneno,");
            values.append("'"+eventDto.getnLaneNo()+"',");
        }
        if(!isEmpty(eventDto.getBefor())){
            filed.append("befor,");
            values.append("'"+eventDto.getBefor()+"',");
        }

        if(!isEmpty(eventDto.getUsetime())){
            filed.append("usetime,");
            values.append("'"+eventDto.getUsetime()+"',");
        }

        if(!isEmpty(eventDto.getAlltime())){
            filed.append("alltime,");
            values.append("'"+eventDto.getAlltime()+"',");
        }

        if(!isEmpty(eventDto.getLat())){
            filed.append("lat,");
            values.append("'"+eventDto.getLat()+"',");
        }
        if(!isEmpty(eventDto.getLon())){
            filed.append("lon,");
            values.append("'"+eventDto.getLon()+"',");
        }

        if(!isEmpty(eventDto.getDelay())){
            filed.append("delay,");
            values.append("'"+eventDto.getDelay()+"',");
        }
        if(!isEmpty(eventDto.getName())){
            filed.append("name,");
            values.append("'"+eventDto.getName()+"',");
        }
        if(!isEmpty(eventDto.getChannel())){
            filed.append("channel,");
            values.append("'"+eventDto.getChannel()+"',");
        }
        if(!isEmpty(eventDto.getSog())){
            filed.append("sog,");
            values.append("'"+eventDto.getSog()+"',");
        } if(!isEmpty(eventDto.getCog())){
            filed.append("cog,");
            values.append("'"+eventDto.getCog()+"',");
        } if(!isEmpty(eventDto.getHeading())){
            filed.append("heading,");
            values.append("'"+eventDto.getHeading()+"',");
        } if(!isEmpty(eventDto.getMmsi())){
            filed.append("mmsi,");
            values.append("'"+eventDto.getMmsi()+"',");
        } if(!isEmpty(eventDto.getImo())){
            filed.append("imo,");
            values.append("'"+eventDto.getImo()+"',");
        } if(!isEmpty(eventDto.getCallsign())){
            filed.append("callsign,");
            values.append("'"+eventDto.getCallsign()+"',");
        } if(!isEmpty(eventDto.getShipTypeName())){
            filed.append("shipTypeName,");
            values.append("'"+eventDto.getShipTypeName()+"',");
        } if(!isEmpty(eventDto.getWidth())){
            filed.append("width,");
            values.append("'"+eventDto.getWidth()+"',");
        } if(!isEmpty(eventDto.getLength())){
            filed.append("length,");
            values.append("'"+eventDto.getLength()+"',");
        } if(!isEmpty(eventDto.getTimeout())){
            filed.append("timeout,");
            values.append("'"+eventDto.getTimeout()+"',");
        } if(!isEmpty(eventDto.getShipTypeClassName())){
            filed.append("shipTypeClassName,");
            values.append("'"+eventDto.getShipTypeClassName()+"',");
        } if(!isEmpty(eventDto.getInlandShipMark())){
            filed.append("inlandShipMark,");
            values.append("'"+eventDto.getInlandShipMark()+"',");
        } if(!isEmpty(eventDto.getNlane())){
            filed.append("nlane,");
            values.append("'"+eventDto.getNlane()+"',");
        } if(!isEmpty(eventDto.getDirection())){
            filed.append("direction,");
            values.append("'"+eventDto.getDirection()+"',");
        } if(!isEmpty(eventDto.getOrderno())){
            filed.append("orderno,");
            values.append("'"+eventDto.getOrderno()+"',");
        } if(!isEmpty(eventDto.getOrdercar())){
            filed.append("ordercar,");
            values.append("'"+eventDto.getOrdercar()+"',");
        }if(!isEmpty(eventDto.getState())){
            filed.append("state,");
            values.append("'"+eventDto.getState()+"',");
        }if(!isEmpty(eventDto.getUtc())){
            filed.append("utc,");
            values.append("'"+ eventDto.getUtc() +"',");
        }
        filed.append("time");
        values.append("'"+ eventDto.getTime() +"'");
        table = table + filed.toString() + ")  values (" + values.toString() +")";

        // 1. 准备必要的变量
        int affectedRows = 0;
        Connection connection = null;
        PreparedStatement statement = null;

        // 2. 利用 util.JdbcUtils 工具类获取数据库连接对象
        connection = JdbcUtils.getConnection();

        try {
            statement = handlePreparedStatement(connection, table, null);
            // 5. 执行 SQL 语句，得到受影响的行数
            affectedRows = statement.executeUpdate();
            // 数据备份
            String table2 = table.replace("trajectory","trajectory_1");
            PreparedStatement statement2 = handlePreparedStatement(connection, table2, null);
            statement2.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.close(connection, statement);
        }

        return affectedRows;
    }

    /**
     * 更新方法，支持 insert delete update 操作
     *
     * @return SQL 语句执行对应数据表的影响行数。
     */
    public static int insertTraFic(String sql) {

        // 1. 准备必要的变量
        int affectedRows = 0;
        Connection connection = null;
        PreparedStatement statement = null;

        // 2. 利用 util.JdbcUtils 工具类获取数据库连接对象
        connection = JdbcUtils.getConnection();

        try {
            statement = handlePreparedStatement(connection, sql, null);
            // 5. 执行 SQL 语句，得到受影响的行数
            affectedRows = statement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.close(connection, statement);
        }

        return affectedRows;
    }

    /**
     * 更新方法，支持 insert delete update 操作
     *
     * @return SQL 语句执行对应数据表的影响行数。
     */
    public static int updateTrajectory() {

        // 1. 准备必要的变量
        int affectedRows = 0;
        Connection connection = null;
        PreparedStatement statement = null;

        // 2. 利用 util.JdbcUtils 工具类获取数据库连接对象
        connection = JdbcUtils.getConnection();

        try {

            String sql1 = "update trajectory t " +
                    " set t.mmsi=t.id-t.orderno,t.state=1  where t.state=0";

            statement = handlePreparedStatement(connection, sql1, null);
            affectedRows = statement.executeUpdate();

//            String sql2 = "update  trajectory t set t.status=case  when t.direction='右转' then 1 \n" +
//                    " when  TIMESTAMPDIFF(SECOND,t.green,t.time)% t.alltime>=t.befor and  TIMESTAMPDIFF(SECOND,t.green,t.time)% t.alltime<=t.befor+t.usetime then 1 else 0 end \n" +
//                    " where t.status is null";
//            String sql2 = "update  trajectory t set t.status=case  when t.direction='右转' then 1 \n" +
//                    " when  TIMESTAMPDIFF(SECOND,t.green,t.time)% t.alltime>=t.befor and  TIMESTAMPDIFF(SECOND,t.green,t.time)% t.alltime<=t.befor+t.usetime then 1 else 0 end \n" +
//                    " where t.status is null";
//
//            statement = handlePreparedStatement(connection, sql2, null);
//            affectedRows = statement.executeUpdate();

//            String sql3 = "delete FROM trajectory e where e.mmsi in ( SELECT mmsi from(select t.mmsi from trajectory t where t.status=0) b )  ";
//            statement = handlePreparedStatement(connection, sql3, null);
//            affectedRows = statement.executeUpdate();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.close(connection, statement);
        }

        return affectedRows;
    }


    public static int insert(TrafficFlowStateDto trafficFlowStateDto) {
        String sql = "INSERT INTO traffic_flow_state ( n_channel_id,n_event_id,n_lane,dw_state,dw_flow,f_average_speed,em_driving_dir,utc) " +
                "values(%s,%s,%s,%s,%s,%s,%s,'%s')";
        String insertSql = String.format(sql,
                trafficFlowStateDto.getnChannelID(),
                trafficFlowStateDto.getnEventId(),
                trafficFlowStateDto.getnLane(),
                trafficFlowStateDto.getDwState(),
                trafficFlowStateDto.getDwFlow(),
                trafficFlowStateDto.getfAverageSpeed(),
                trafficFlowStateDto.getEmDrivingDir(),
                trafficFlowStateDto.getUtc());

        System.out.println("交通流量插入sql = "+insertSql);

        // 1. 准备必要的变量
        int affectedRows = 0;
        Connection connection = null;
        PreparedStatement statement = null;

        // 2. 利用 util.JdbcUtils 工具类获取数据库连接对象
        connection = JdbcUtils.getConnection();

        try {
            statement = handlePreparedStatement(connection, insertSql, null);
            // 5. 执行 SQL 语句，得到受影响的行数
            affectedRows = statement.executeUpdate();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.close(connection, statement);
        }

        return affectedRows;
    }


    //Object... parameters   意思是多个Object类型数据
    public static PreparedStatement getPreparedStatement(String sql,Connection connection,Object... parameters) throws SQLException {

        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //获得元数据
        ParameterMetaData parameterMetaData = preparedStatement.getParameterMetaData();
        //获得字符串需要的参数个数
        int count = parameterMetaData.getParameterCount();
        //判断什么情况才需要给preparedStatement赋值
        if(count != 0 && parameters != null && parameters.length == count){
            for (int i = 0; i < count; i++) {
                preparedStatement.setObject(i + 1, parameters[i]);
            }
        }
        return preparedStatement;
    }


    public static List<Map<String, Object>>  queryBean(String sql, Object... parameters) throws SQLException {
        // 1. 必要变量
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;


        List<Map<String, Object>> resultList = new ArrayList<Map<String,Object>>();
        Map<String, Object> map  = null;

        // 2. 获取数据库连接
        connection = JdbcUtils.getConnection();
        try {
            statement = getPreparedStatement(sql, connection, parameters);
            // 4. 执行查询操作，得到 ResultSet 结果集对象
            resultSet = statement.executeQuery();
            // 【核心】获取结果集元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            while (resultSet.next()) {          //resultSet结果集中有个指针，指针下移指向结果集的下一行元素，且若有元素返回true
                // 实例化泛型约束之后对应具体数据类型对象
                //  t = cls.getConstructor().newInstance();
                map = new HashMap<String, Object>();
                // 字段个数
                int columnCount = metaData.getColumnCount();
                for (int i = 1; i <= columnCount; i++) {
                    map.put(metaData.getColumnName(i)+"",  resultSet.getObject(i));
                }
                resultList.add(map);
            }
        } catch (SQLException e) {
            // SQL 异常，保留原类型进行抛出操作
            throw e;
        } catch (Exception e) {
            // 反射操作异常，包装为 RuntimeException 运行时异常 抛出
            throw new RuntimeException(e);
        } finally {
            //System.out.println("关闭数据库链接");
            // JdbcUtils.close(connection, statement, resultSet);
        }
        return resultList;
    }


    /**
     * 通用 query 查询方法
     *
     * @param sql        要执行的SQL语句
     * @param parameters SQL语句对应参数
     * @return 接口实现类约束的返回值类型结果
     * @throws SQLException SQL 异常
     */
    public static Object query(String sql, Object... parameters) throws SQLException {
        //准备必要变量
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        //  获取数据库连接对象
        connection = JdbcUtils.getConnection();

        try {
            //上面封装的handlePreparedStatement方法，可以完成 SQL 语句预处理和参数赋值操作
            statement = handlePreparedStatement(connection, sql, parameters);

            // 执行SQL语句，得到结果集对象
            resultSet = statement.executeQuery();

            // 处理结果集对象，返回值类型由实现类决定
            while (resultSet.next()){

            }

        } catch (SQLException e) {
            throw e;
        } finally {
            JdbcUtils.close(connection, statement, resultSet);
        }

        return resultSet;
    }

    public static void main(String[] args) {
        Connection connection = getConnection();
        System.out.println(connection);
    }
}

